﻿CREATE PROCEDURE [vantage].[s_Admin_CreateObjects_20] AS


--IF EXISTS(SELECT * FROM vantage.t_Note HAVING DATEDIFF(dd,MAX(ImportDate),GETDATE())>0)
--DELETE FROM vantage.t_Note WHERE NoteDate > '10/26/2009'
INSERT INTO vantage.t_Note([Target_NoteID]
      ,[AccountNumber]
      ,[NoteSourceRef]
      ,[NoteTypeRef]
      ,[ActionTypeRef]
      ,[ResultTypeRef]
      ,[NoteImportanceRef]
      ,[NoteDate]
      ,[IsPublic]
      ,[Notes]
      ,[CreatedDate]
      ,[CreatedUser])
SELECT [Target_NoteID]
      ,[AccountNumber]
      ,[NoteSourceRef]
      ,[NoteTypeRef]
      ,[ActionTypeRef]
      ,[ResultTypeRef]
      ,[NoteImportanceRef]
      ,[NoteDate]
      ,[IsPublic]
      ,[Notes]
      ,[CreatedDate]
      ,[CreatedUser]
FROM [Vantage_In].[dbo].[Target_Note]
ORDER BY [NoteDate]


TRUNCATE TABLE vantage.t_NoteLast
INSERT INTO vantage.t_NoteLast
SELECT     AccountNumber, NoteSourceRef, NoteTypeRef, ActionTypeRef, ResultTypeRef, NoteImportanceRef, NoteDate, IsPublic, Notes, CreatedDate, 
                      CreatedUser, ImportDate, DaysSince
FROM         vantage.v_NoteLast

SELECT     VantageID,CompanyID,UsixID, LoanClientID, LoanID, ApplicationNumber, CompanyRef, BranchCodeRef, LoanProgramRef, ProductRef, JurisdictionRef, PortfolioRef, 
                      DealerName, InterestTypeRef, CurrentStatusRef, CurrentStatusDate, CollateralStatusRef, CollateralStatusDate, OriginalAmountFinanced, 
                      AmountFinanced, OpenDate, FirstDueDate, LoanTerm, OriginalLoanTerm, OriginalInterestRate, InterestRate, OriginalAPR, APR, OriginalDueDay, 
                      DueDay, OriginalPaymentAmount, PaymentAmount, PaymentFrequencyRef, OriginalFinanceCharge, FinanceCharge, DiscountAmount, OriginalReserve, 
                      OriginalWithHold, PurchaseDate, PurchasePrincipalPaid, PurchaseInterest, PurchaseInterestPaid, LoanFundingDate, NextDueDate, MaturityDate, 
                      GrossBalance, InterestAccrued, InterestPaid, PrinicpalPaid, FeePaid, PastDueInterest, CurrentDueInterest, PastDuePrincipal, CurrentDuePrincipal, 
                      NSFFeeBalance, LateFeeBalance, LegalFeeBalance, PaymentMethodFeeBalance, RepoFeeBalance, InsuranceFeeBalance, OtherFeeBalance, 
                      DefermentFeeBalance, DueDayChangeFeeBalance, CollectionFeeBalance, RecoveryFeeBalance, Make, Model, ModelYear, VIN, CashPrice, 
                      MilageAtPurchase, GPSTrackerID, BVAtPurchase, Milage, MilageAsOfDate, BV, BVAsOfDate, TitleStateRef, IsRegistered, IsTitleReceived, 
                      RelationshipTypeRef, FirstName, LastName, ClientName, SSN, DLN, DOB, FICO, MaritalStatusRef, Occupation, PrimaryLanguageRef, TimeZoneRef, 
                      EntityStatusRef, Street, AptSuite, City, CountyRef, StateRef, ZipCode, DPD, PastDueAmount, LastPaymentDate, DSLP, PayOffAmount, PaymentsMade, 
                      PaymentsPerYear, PrincipalBalance, PerDiem,PhoneHome,PhoneWork,PhoneMobile,PhoneOther,DaysSinceLastNote
INTO #BRIDGE
FROM         vantage.v_Bridge_20 WITH(nolock)

BEGIN TRAN
TRUNCATE TABLE vantage.t_Bridge_20
INSERT INTO vantage.t_Bridge_20
SELECT     VantageID,CompanyID,UsixID, LoanClientID, LoanID, ApplicationNumber, CompanyRef, BranchCodeRef, LoanProgramRef, ProductRef, JurisdictionRef, PortfolioRef, 
                      DealerName, InterestTypeRef, CurrentStatusRef, CurrentStatusDate, CollateralStatusRef, CollateralStatusDate, OriginalAmountFinanced, 
                      AmountFinanced, OpenDate, FirstDueDate, LoanTerm, OriginalLoanTerm, OriginalInterestRate, InterestRate, OriginalAPR, APR, OriginalDueDay, 
                      DueDay, OriginalPaymentAmount, PaymentAmount, PaymentFrequencyRef, OriginalFinanceCharge, FinanceCharge, DiscountAmount, OriginalReserve, 
                      OriginalWithHold, PurchaseDate, PurchasePrincipalPaid, PurchaseInterest, PurchaseInterestPaid, LoanFundingDate, NextDueDate, MaturityDate, 
                      GrossBalance, InterestAccrued, InterestPaid, PrinicpalPaid, FeePaid, PastDueInterest, CurrentDueInterest, PastDuePrincipal, CurrentDuePrincipal, 
                      NSFFeeBalance, LateFeeBalance, LegalFeeBalance, PaymentMethodFeeBalance, RepoFeeBalance, InsuranceFeeBalance, OtherFeeBalance, 
                      DefermentFeeBalance, DueDayChangeFeeBalance, CollectionFeeBalance, RecoveryFeeBalance, Make, Model, ModelYear, VIN, CashPrice, 
                      MilageAtPurchase, GPSTrackerID, BVAtPurchase, Milage, MilageAsOfDate, BV, BVAsOfDate, TitleStateRef, IsRegistered, IsTitleReceived, 
                      RelationshipTypeRef, FirstName, LastName, ClientName, SSN, DLN, DOB, FICO, MaritalStatusRef, Occupation, PrimaryLanguageRef, TimeZoneRef, 
                      EntityStatusRef, Street, AptSuite, City, CountyRef, StateRef, ZipCode, DPD, PastDueAmount, LastPaymentDate, DSLP, PayOffAmount, PaymentsMade, 
                      PaymentsPerYear, PrincipalBalance, PerDiem,PhoneHome,PhoneWork,PhoneMobile,PhoneOther,DaysSinceLastNote
FROM #BRIDGE
COMMIT

RETURN --======================================================================================
TRUNCATE TABLE vantage.t_PtpStatus
INSERT INTO vantage.t_PtpStatus(
AccountNumber,
PaymentAmount,
Target_PromiseToPayID,
PromiseDueDate,
PtpStatus,
CreatedUser
)
SELECT     PTP.AccountNumber, LC.PaymentAmount, PTP.Target_PromiseToPayID, PTP.PromiseDueDate,
dbo.PtpStatus(PTP.AccountNumber, LC.PaymentAmount, PTP.PromiseDueDate, PTP.CreatedUser, DEFAULT, DEFAULT) AS PtpStatus,
PTP.CreatedUser

FROM         Vantage_In.dbo.Target_PromiseToPay AS PTP
INNER JOIN Vantage_In.dbo.Target_LoanContract AS LC ON PTP.AccountNumber = LC.AccountNumber


